﻿Imports System.Data
Imports System.Data.SqlClient

Public Class MonHocMoDAO
    Private Shared lenhInsert As SqlCommand
    Private Shared lenhUpdate As SqlCommand
    Private Shared lenhDelete As SqlCommand
    Private dataProvider As DataProvider
    Private sqlstr As String
    Public Sub New()
        dataProvider = New DataProvider
    End Sub
    Public Shared Function GetData() As DataTable
        Dim dt As DataTable = dataProvider.Executequery("sp_XuatMonHocMo")
        Return dt
    End Function
    Public Shared Sub ThemMHM(ByVal mhm As MonHocMoDTO)
        Dim sql As String

        sql = "INSERT INTO MONHOCMO(SiSoToiDa,MaHocKy,SiSoHienTai,MaGiangVien,MaMonHoc) VALUES(@SiSoToiDa,@MaHocKy,@SiSoHienTai,@MaGiangVien,@MaMonHoc)"
        lenhInsert = New SqlCommand()
        lenhInsert.CommandText = sql
        Dim thamSo As SqlParameter

        thamSo = New SqlParameter("@SiSoToiDa", SqlDbType.Int)
        thamSo.SourceColumn = mhm.SiSoToiDa
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@SiSoHienTai", SqlDbType.Int)
        thamSo.SourceColumn = mhm.SiSoHienTai
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@MaGiangVien", SqlDbType.VarChar)
        thamSo.SourceColumn = mhm.MaGiangVien
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@MaHocKy", SqlDbType.Int)
        thamSo.SourceColumn = mhm.MaHocKy
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@MaMonHoc", SqlDbType.VarChar)
        thamSo.SourceColumn = mhm.MaMonHoc
        lenhInsert.Parameters.Add(thamSo)

    End Sub
    Public Shared Sub UpDateMHM(ByVal mhm As MonHocMoDTO)
        Dim sql As String

        sql = "UPDATE MONHOCMO SET SiSoToiDa=@SiSoToiDa,MaHocKy=@MaHocKy,SiSoHienTai=@SiSoHienTai,MaGiangVien=@MaGiangVien,MaMonHoc=@MaMonHoc WHERE MaMonHocMo=@MaMonHocMo"
        lenhUpdate = New SqlCommand()
        lenhUpdate.CommandText = sql
        Dim thamSo As SqlParameter

        thamSo = New SqlParameter("@SiSoToiDa", SqlDbType.Int)
        thamSo.SourceColumn = mhm.SiSoToiDa
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@SiSoHienTai", SqlDbType.Int)
        thamSo.SourceColumn = mhm.SiSoHienTai
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@MaGiangVien", SqlDbType.VarChar)
        thamSo.SourceColumn = mhm.MaGiangVien
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@MaHocKy", SqlDbType.Int)
        thamSo.SourceColumn = mhm.MaHocKy
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@MaMonHoc", SqlDbType.VarChar)
        thamSo.SourceColumn = mhm.MaMonHoc
        lenhUpdate.Parameters.Add(thamSo)
    End Sub
    Public Shared Sub DeleteMHM(ByVal qlmhmDTO As MonHocMoDTO)
        Dim sql As String

        sql = "DELETE FROM  MONHOCMO WHERE MaMonHocMo=@MaMonHocMo"
        lenhDelete = New SqlCommand()
        lenhDelete.CommandText = sql
        Dim thamSo As SqlParameter

        thamSo = New SqlParameter("@MaMonHocMo", SqlDbType.VarChar)
        thamSo.SourceColumn = "MaMonHocMo"
        lenhDelete.Parameters.Add(thamSo)
    End Sub
    Public Shared Sub CapNhat(ByVal bangMONHOCMO As DataTable)
        'dataProvider.GhiBang(bangMONHOCMO, "MONHOCMO", lenhInsert, lenhUpdate, lenhDelete)
        dataProvider.GhiBang(bangMONHOCMO, "MONHOCMO")
    End Sub

    Public Shared Function LayDSMONHOCMO() As DataTable
        Dim bangMONHOCMO As DataTable = dataProvider.DocBang("SELECT MaMonHocMo,SiSoToiDa,SiSoHienTai,MHM.MaGiangVien AS MaGiangVien, HoTen, MHM.MaHocKy AS MaHocKy, Nam,MHM.MaMonHoc AS MaMonHoc, TenMonHoc FROM MONHOCMO MHM,GIANGVIEN GV, HOCKY HK, MONHOC MH WHERE MHM.MaGiangVien=GV.MaGiangVien AND MHM.MaHocKy=HK.MaHocKy AND MHM.MaMonHoc=MH.MaMonHoc")
        Return bangMONHOCMO
    End Function

    Public Shared Function LayCauTrucBang() As DataTable
        Return dataProvider.DocCauTruc("SELECT * FROM MONHOCMO")
    End Function

    Public Shared Function TimKiemTheoTen(ByVal maMonHocMo As String) As DataTable

        Dim cn As SqlConnection = dataProvider.GetConnection()
        cn.Open()
        Dim str As String = "select * from MONHOCMO where MaMonHocMo like '" & maMonHocMo & "%' "
        Dim da As SqlDataAdapter = New SqlDataAdapter(str, cn)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt
    End Function
End Class
